package it.Dao;


import it.domain.User;
import it.domain.Userbook;
import it.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 阿里木·买斯地克
 * @create 2023-06-31-14:16
 */
public class UserbookDao {
    QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());

    /**
     * 根据用户名获取id
     *
     * @param username
     * @return
     * @throws SQLException
     */
    public Integer getIdByname(String username) throws SQLException {
        String sql = "select id from user where username=?";
        Integer uid = (Integer) queryRunner.query(sql, new ScalarHandler(), username);
        return uid;
    }
    /**
     * 根据图书id获取图书信息
     *
     * @param bid
     * @return
     * @throws SQLException
     */
    public List<String> getBookInfo(Integer bid) throws SQLException {
        List<String> list = new ArrayList<String>();
        String sql = "select * from books where id=?";
        Connection connection = DBUtils.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, bid);
        ResultSet resultSet = ps.executeQuery();
        String name = null;
        String author = null;
        String category = null;
        if (resultSet.next()) {
            name = resultSet.getString("name");
            author = resultSet.getString("author");
            category = resultSet.getString("category");
        }
        list.add(name);
        list.add(author);
        list.add(category);
        //System.out.println(list);
        return list;
    }

    /**
     * 根据信息更新用户以及书籍关系的表
     *
     * @param uid
     * @param username
     * @param bid
     * @param bookList
     * @param duration
     * @return
     * @throws SQLException
     */
    public int updateUB(Integer uid, String username, Integer bid, List<String> bookList, String duration) throws SQLException {
        String sql = "insert into userbook values(?,?,?,?,?,?,?,?,?,?,?)";
        //获取当前时间
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = sdf.format(date);
        int update = queryRunner.update(sql, null, uid, bid, username, bookList.get(0),
                bookList.get(1), bookList.get(2), time, null, duration,0);
        return update;
    }

    /**
     * 查询表中用户名为user的记录
     *
     * @param user
     * @throws SQLException
     */
    public Long getTotalCount(String user) throws SQLException {
        String sql = "select count(*) from userbook where username like ?";
        String userT="%"+user+"%";
        Long query = (Long) queryRunner.query(sql, new ScalarHandler(), userT);
        return query;
    }

    public Long getATotalCount(String user) throws SQLException {
        String sql = "select count(*) from userbook where username=?";
        Long query = (Long) queryRunner.query(sql, new ScalarHandler(), user);
        return query;
    }

    /**
     * 获取表中名为user的数据
     * @param user
     * @param currentPage
     * @param counts
     * @return
     * @throws SQLException
     */
    public List<Userbook> getUBList(String user, Integer currentPage, Integer counts) throws SQLException {
        String sql = "select * from userbook where username like ? order by id desc limit ?,?";
        String userT="%"+user+"%";
        List<Userbook> list = queryRunner.query(sql, new BeanListHandler<Userbook>(Userbook.class),
                userT, (currentPage - 1) * counts, counts);
        return list;
    }

    public List<Userbook> getAUBList(String user, Integer currentPage, Integer counts) throws SQLException {
        String sql = "select * from userbook where username =? order by id desc limit ?,?";
        List<Userbook> list = queryRunner.query(sql, new BeanListHandler<Userbook>(Userbook.class),
                user, (currentPage -1) * counts, counts);
        return list;
    }

    /**
     * 更新续借时间
     *
     * @param id
     * @param duration
     * @return
     * @throws SQLException
     */
    public int updateConBoTime(Integer id, String duration) throws SQLException {
        String sql = "update userbook set duration=? where id=?";
        int update = queryRunner.update(sql, duration, id);
        return update;
    }

    /**
     * 更新还书时间并且更新flag
     * @param id
     * @return
     * @throws SQLException
     */
    public int updateReturnTime(Integer id) throws SQLException {
        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = sdf.format(date);
        String sql = "update userbook set returntime=?,flag=? where id=?";
        int update = queryRunner.update(sql, time, 1,id);
        return update;
    }

    /**
     * 根据用户名获取用户信息
     * @param username
     * @return
     * @throws SQLException
     */
    public User getUserByName(String username) throws SQLException {
        String sql = "select * from user where username=?";
        User user = queryRunner.query(sql, new BeanHandler<User>(User.class), username);
        return user;
    }

    /**
     * 更改用户信息
     * @param user
     * @return
     * @throws SQLException
     */
    public int modifyUser(User user) throws SQLException {
        String sql = "update user set username=?,password=?,name=?,email=?,phone=? where username=?";
        int update = queryRunner.update(sql, user.getUsername(), user.getPassword(), user.getName(),
                user.getEmail(), user.getPhone(), user.getUsername());
        return update;
    }

    /**
     * 查询总记录数
     * @return
     * @throws SQLException
     */
    public Long getUBTotalCount() throws SQLException {
        String sql="select count(*) from userbook";
        Long query = (Long) queryRunner.query(sql, new ScalarHandler());
        return query;
    }

    /**
     * 根据用户名查询总记录数
     * @param user
     * @return
     * @throws SQLException
     */
    public Long getUBTotalCountByName(String user) throws SQLException {
        String sql="select count(*) from userbook where username like ?";
        String userT="%"+user+"%";
        Long query = (Long) queryRunner.query(sql, new ScalarHandler(), userT);
        return query;
    }

    /**
     * 查询所有记录
     * @param currentPage
     * @param counts
     * @return
     * @throws SQLException
     */
    public List<Userbook> getUBAllList(Integer currentPage, Integer counts) throws SQLException {
        String sql="select * from userbook order by id desc limit ?,?";
        List<Userbook> list = queryRunner.query(sql, new BeanListHandler<Userbook>(Userbook.class), (currentPage - 1) * counts, counts);
        return list;
    }

    /**
     * 删除一条记录
     * @param id
     * @return
     * @throws SQLException
     */
    public int deleteUB(Integer id) throws SQLException {
        String sql="delete from userbook where id=?";
        int update = queryRunner.update(sql, id);
        return update;
    }
}
